# -*- coding: utf-8 -*-
import sys
sys.path.append('../utils/')
sys.path.append('./utils/')
from mysql_connector import Connector

def transfer(tenantID) :
    print("开始导入数据 tpcc_table --> universal table")
    try:
        conn = Connector()
        conn.connect()
        cur = conn.cursor()

        # 转移warehouse
        sql = "select * from warehouse"
        cur.execute(sql)

        for row in cur.fetchall():
            cur.execute(
                "insert into saas_data(tableID,tenantID,value1,value2,value3,value4,value5,value6,value7,value8,value9) values('" + str(
                    1) + "','" + str(tenantID) + "','" + str(row[0]) + "','" + str(row[1]) + "','" + str(row[2]) + "','" + str(
                    row[3]) + "','" + str(row[4]) + "','" + str(row[5]) + "','" + str(row[6]) + "','" + str(
                    row[7]) + "','" + str(row[8]) + "')")
            # print(row)
        conn.commit()
        print("转移warehouse表完成")

        # 转移district
        sql = "select * from district"
        cur.execute(sql)

        for row in cur.fetchall():
            cur.execute(
                "insert into saas_data(tableID,tenantID,value1,value2,value3,value4,value5,value6,value7,value8,value9,"
                "value10,value11) "
                "values('" + str(2) + "','" + str(tenantID) + "','" + str(row[0]) + "','" + str(row[1]) + "','" + str(
                    row[2]) + "','"
                + str(row[3]) + "','" + str(row[4]) + "','" + str(row[5]) + "','" + str(row[6]) + "','" + str(
                    row[7]) + "','"
                + str(row[8]) + "','" + str(row[9]) + "','" + str(row[10]) + "')")
            # print(row)
        conn.commit()
        print("转移district表完成")

        # 转移customer
        sql = "select * from customer"
        cur.execute(sql)

        for row in cur.fetchall():
            cur.execute(
                "insert into saas_data(tableID,tenantID,value1,value2,value3,value4,value5,value6,value7,value8,value9,"
                "value10,value11,value12,value13,value14,value15,value16,value17,value18,value19,value20,value21) "
                "values('" + str(3) + "','" + str(tenantID) + "','" + str(row[0]) + "','" + str(row[1]) + "','" + str(
                    row[2]) + "','"
                + str(row[3]) + "','" + str(row[4]) + "','" + str(row[5]) + "','" + str(row[6]) + "','" + str(
                    row[7]) + "','"
                + str(row[8]) + "','" + str(row[9]) + "','" + str(row[10]) + "','" + str(row[11]) + "','" + str(
                    row[12]) + "','"
                + str(row[13]) + "','" + str(row[14]) + "','" + str(row[15]) + "','" + str(row[16]) + "','" + str(
                    row[17]) + "','"
                + str(row[18]) + "','" + str(row[19]) + "','" + str(row[20]) + "')")
            # print(row)
        conn.commit()
        print("转移customer表完成")

        # 转移history
        sql = "select * from history"
        cur.execute(sql)

        for row in cur.fetchall():
            cur.execute(
                "insert into saas_data(tableID,tenantID,value1,value2,value3,value4,value5,value6,value7,value8) "
                "values('" + str(4) + "','" + str(tenantID) + "','" + str(row[0]) + "','" + str(row[1]) + "','" + str(
                    row[2]) + "','"
                + str(row[3]) + "','" + str(row[4]) + "','" + str(row[5]) + "','" + str(row[6]) + "','" + str(
                    row[7]) + "')")
            # print(row)
        conn.commit()
        print("转移history表完成")

        # 转移new_orders
        sql = "select * from new_orders"
        cur.execute(sql)

        for row in cur.fetchall():
            cur.execute(
                "insert into saas_data(tableID,tenantID,value1,value2,value3) "
                "values('" + str(5) + "','" + str(tenantID) + "','" + str(row[0]) + "','" + str(row[1]) + "','" + str(
                    row[2]) + "')")
            # print(new_orders)
        conn.commit()
        print("转移new_orders表完成")

        # 转移orders
        sql = "select * from orders"
        cur.execute(sql)

        for row in cur.fetchall():
            cur.execute(
                "insert into saas_data(tableID,tenantID,value1,value2,value3,value4,value5,value6,value7,value8) "
                "values('" + str(6) + "','" + str(tenantID) + "','" + str(row[0]) + "','" + str(row[1]) + "','" + str(
                    row[2]) + "','"
                + str(row[3]) + "','" + str(row[4]) + "','" + str(row[5]) + "','" + str(row[6]) + "','" + str(
                    row[7]) + "')")
            # print(row)
        conn.commit()
        print("转移orders表完成")

        # 转移order_line
        sql = "select * from order_line"
        cur.execute(sql)

        for row in cur.fetchall():
            cur.execute(
                "insert into saas_data(tableID,tenantID,value1,value2,value3,value4,value5,value6,value7,value8,value9,"
                "value10) "
                "values('" + str(7) + "','" + str(tenantID) + "','" + str(row[0]) + "','" + str(row[1]) + "','" + str(
                    row[2]) + "','"
                + str(row[3]) + "','" + str(row[4]) + "','" + str(row[5]) + "','" + str(row[6]) + "','" + str(
                    row[7]) + "','"
                + str(row[8]) + "','" + str(row[9]) + "')")
            # print(row)
        conn.commit()
        print("转移order_line表完成")

        # 转移item
        sql = "select * from item"
        cur.execute(sql)

        for row in cur.fetchall():
            cur.execute(
                "insert into saas_data(tableID,tenantID,value1,value2,value3,value4,value5) "
                "values('" + str(8) + "','" + str(tenantID) + "','" + str(row[0]) + "','" + str(row[1]) + "','" + str(
                    row[2]) + "','"
                + str(row[3]) + "','" + str(row[4]) + "')")
            # print(row)
        conn.commit()
        print("转移item表完成")

        # 转移stock
        sql = "select * from stock"
        cur.execute(sql)

        for row in cur.fetchall():
            cur.execute(
                "insert into saas_data(tableID,tenantID,value1,value2,value3,value4,value5,value6,value7,value8,value9,"
                "value10,value11,value12,value13,value14,value15,value16,value17) "
                "values('" + str(9) + "','" + str(tenantID) + "','" + str(row[0]) + "','" + str(row[1]) + "','" + str(
                    row[2]) + "','"
                + str(row[3]) + "','" + str(row[4]) + "','" + str(row[5]) + "','" + str(row[6]) + "','" + str(
                    row[7]) + "','"
                + str(row[8]) + "','" + str(row[9]) + "','" + str(row[10]) + "','" + str(row[11]) + "','" + str(
                    row[12]) + "','"
                + str(row[13]) + "','" + str(row[14]) + "','" + str(row[15]) + "','" + str(row[16]) + "')")
            # print(row)
        conn.commit()
        print("转移stock表完成")
        print("tpcc_table --> universal table 数据转移完成")

    except Exception as e:
        print('load sql')
        print('e\t' + str(e))

    finally:
        #cur.close()
        conn.close()

if __name__ == "__main__":
    transfer(1)
